Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports Npgsql

Namespace Persistencia.ModuloAdministracion

    Public Class bd

#Region "Atributos"

        Private Shared instance As bd
        Private conn As NpgsqlConnection
        Private stringConnection As String = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=golden;Database=bdmsh;"

#End Region

        'Constructor
        Public Sub New()
            Try
                conn = New NpgsqlConnection(stringConnection)
            Catch ex As NpgsqlException
                MsgBox(ex)
            End Try
        End Sub

#Region "getInstance"
        'Patron Singleton 
        Public Shared Function getInstance() As bd

            If instance Is Nothing Then
                instance = New bd
            End If

            Return instance

        End Function
#End Region

#Region "funciones"
        Private Sub Open()
            conn.Open()
        End Sub

        Private Sub Close()
            conn.Close()
        End Sub

        Public Function EjecutarConRetorno(ByVal query As String) As Hashtable
            Dim array As New Hashtable
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()

                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()

                Dim i As Integer
                While dr.Read()
                    For i = 0 To dr.FieldCount - 1
                        Dim dato As String = CStr(dr(dr.GetName(i).ToString))
                        Dim nombrecampo As String = dr.GetName(i).ToString
                        array.Add(nombrecampo, dato)
                    Next i
                End While

                Me.Close()

                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing

        End Function

        Public Sub EjecutarSinRetorno(ByVal query As String)
            Try
                Me.Open()
                Dim command As New NpgsqlCommand(query, conn)
                command.ExecuteNonQuery()
                Me.Close()
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try

        End Sub

        Public Function obtenerCliente(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Cliente
                While dr.Read()
                    Objeto = New Cliente(CStr(dr("rut")), CStr(dr("rznsocial")), _
                    CStr(dr("direccion")), _
                    CStr(dr("giro")), _
                    CStr(dr("telefono")), _
                    CStr(dr("comuna")), _
                    CStr(dr("region")), _
                    CStr(dr("contacto")), _
                    CStr(dr("ciudad")), _
                    CStr(dr("fax")), _
                    CStr(dr("email")), _
                    CStr(dr("id")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerPresupuesto(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Presupuesto
                While dr.Read()

                    Objeto = New Presupuesto(CStr(dr("id")), CStr(dr("fecha")), CStr(dr("condventa")), CStr(dr("atencion")), CStr(dr("garantias")), CStr(dr("entrega")), CStr(dr("plano")), _
                                             CStr(dr("neto")), CStr(dr("iva")), CStr(dr("total")), CStr(dr("gastosgeneralesporcentaje")), CStr(dr("utilidadporcentajes")), CStr(dr("utilidadmaterialesporcentaje")), CStr(dr("descuentoporcentaje")), _
                                             CStr(dr("fkcliente")), CStr(dr("fkvendedor")), CStr(dr("requisicion")), CStr(dr("estado")), CInt(dr("cantidadcabezera")), CStr(dr("descripcioncabezera")), CInt(dr("creadopor")), CStr(dr("creadoel")), CStr(dr("estimadosenor")), CStr(dr("precios")), _
                                             CStr(dr("condiciones")), CStr(dr("plazoentrega")), CStr(dr("lugarentrega")), CStr(dr("validezoferta")), CInt(dr("totalficticio")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerUsuario(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Usuario
                While dr.Read()
                    Objeto = New Usuario(CStr(dr("usuario")), CStr(dr("password")))
                    Objeto.RefEmpleado = CStr(dr("fkempleado"))
                    Objeto.Grupo = CStr(dr("fkgrupousuarios"))
                    Objeto.ID = CStr(dr("id"))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerEmpleado(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Empleado
                While dr.Read()
                    Objeto = New Empleado(CStr(dr("nombre")), CStr(dr("apellido")), _
                    CStr(dr("rut")), _
                    CStr(dr("direccion")), _
                    CStr(dr("telefono")), _
                    CStr(dr("profesion")), _
                    CStr(dr("edad")), _
                    CStr(dr("fechainicioempresa")), _
                    CStr(dr("fechafinempresa")), _
                    CStr(dr("sexo")), _
                    CStr(dr("fechanacimiento")), _
                    CStr(dr("nacionalidad")), _
                    CStr(dr("paisnacimiento")), _
                    CStr(dr("regionnacimiento")), _
                    CStr(dr("comunanacimiento")), _
                    CStr(dr("ciudadnacimiento")), _
                    CStr(dr("provinciaresidencia")), _
                    CStr(dr("ciudadresidencia")), _
                    CStr(dr("celular")), _
                    CStr(dr("email")), _
                    CStr(dr("estadocivil")), _
                    CStr(dr("cargasfamiliares")), _
                    CStr(dr("numerocalzado")), _
                    CStr(dr("talla")), CStr(dr("rutafoto")))
                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerGrupo(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As GrupoUsuario
                While dr.Read()
                    Objeto = New GrupoUsuario(CStr(dr("id")), CStr(dr("nombregrupo")), CStr(dr("accesoadministracion")), CStr(dr("accesoinformes")), CStr(dr("accesovistapanol")), CStr(dr("accesobodega")), _
                                            CStr(dr("accesoindicadores")), CStr(dr("accesobusqueda")), CStr(dr("accesorecursoshumanos")), CStr(dr("accesoplanificacion")), CStr(dr("accesocontabilidad")), _
                                            CStr(dr("accesosistema")), CStr(dr("accesosistemageneral")), CStr(dr("accesosolicitudbodega")), CStr(dr("accesosistemapersonal")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing

        End Function

        Public Function obtenerSubProyectoCriterio(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim rutProv As String = ""
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As presupuestosubproyecto
                While dr.Read()
                    Objeto = New presupuestosubproyecto(CStr(dr("id")), CStr(dr("nombresubproyecto")), CStr(dr("descripcionsubproyecto")), CStr(dr("observaciontaller")), CStr(dr("notasalcliente")), _
                                                        CStr(dr("notasventas")), CStr(dr("fkpresupuesto")), CStr(dr("itemaprovado")), CInt(dr("cantidad")), CStr(dr("unidadmedida")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerProveedor(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Proveedor
                While dr.Read()
                    Objeto = New Proveedor(CStr(dr("rut")), CStr(dr("rznsocial")), _
                    CStr(dr("direccion")), CStr(dr("giro")), CStr(dr("telefono")), CStr(dr("comuna")), _
                    CStr(dr("region")), CStr(dr("contacto")), CStr(dr("ciudad")), CStr(dr("fax")), CStr(dr("email")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerFamilia(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Familia
                While dr.Read()
                    Objeto = New Familia()
                    Objeto.Id = CStr(dr("id"))
                    Objeto.Nombre = CStr(dr("nombre"))
                    Objeto.Codigo = CStr(dr("codigo"))
                    Objeto.Descripcion = CStr(dr("descripcion"))
                    Objeto.Bodega = CStr(dr("bodega"))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerActividad(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Actividad
                While dr.Read()
                    Objeto = New Actividad(CInt(dr("id")), CStr(dr("nombre")), _
                    CStr(dr("descripcion")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerMaterialStock(ByVal rutProv As String) As ArrayList

            Dim query As String = "select  DISTINCT mat.id, mat.nombre from material mat, materialdatos md, ordencompra oc " + _
                                "where mat.id = md.fkmaterial and md.fkordencompra = oc.numorden and oc.fkproveedor ='" + rutProv + "'"

            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Material
                While dr.Read()
                    Objeto = New Material()
                    Objeto.Nombre = CStr(dr("nombre"))
                    Objeto.ID = CStr(dr("id"))


                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerMaterialProv(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As MaterialPendiente
                While dr.Read()
                    Objeto = New MaterialPendiente()

                    Objeto.IdMaterialPendiente = CInt(CStr(dr("id")))
                    Objeto.Nombre = CStr(dr("nombre"))
                    Objeto.Estado = CStr(dr("estado"))
                    Objeto.Codigo = CStr(dr("codmaterial"))
                    Objeto.DescripcionFormal = CStr(dr("descripcionformal"))
                    Objeto.DescripcioninFormal = CStr(dr("descripcioninformal"))
                    Objeto.STOCKMINIMO = CStr(dr("stockminimo"))
                    Objeto.RefFAmilia = CStr(dr("reffamilia"))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerPresupuestoSubproyectoMaterial(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As presupuestosubproyectomaterial
                While dr.Read()
                    Objeto = New presupuestosubproyectomaterial(CInt(dr("id")), CInt(dr("cantidad")), CStr(dr("descripcion")), CStr(dr("valorkg")), CInt(dr("fksubproyecto")), _
                            CInt(dr("fkmaterial")), CInt(dr("neto")), CInt(dr("iva")), CInt(dr("totalcobrar")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerPresupuestoSubproyectoActividad(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As presupuestosubproyectoactividad
                While dr.Read()

                    Objeto = New presupuestosubproyectoactividad(CStr(dr("descripcion")), CInt(dr("id")), CInt(dr("cantidadhoras")), CInt(dr("valorhorahombre")), CInt(dr("factorhh")), CInt(dr("prioridadtarea")), _
                             CInt(dr("fksubproyecto")), CInt(dr("fkactividad")), CInt(dr("neto")), CInt(dr("iva")), CInt(dr("totalcobrar")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerOrdenCompra(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As OrdenCompra
                While dr.Read()
                    Objeto = New OrdenCompra(CStr(dr("numorden")), CStr(dr("fechadepedido")), _
                    CStr(dr("fechadepago")))
                    Objeto.rutProv = CStr(dr("fkproveedor"))
                    Objeto.fkRequisicion = CStr(dr("fkrequisicion"))
                    Objeto.Atencion = CStr(dr("atencion"))
                    Objeto.condPago = CStr(dr("condpago"))
                    Objeto.despachar = CStr(dr("despacho"))
                    Objeto.Comprador = CStr(dr("comprador"))
                    Objeto.notas = CStr(dr("notas"))

                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerRequisicion(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim rutProv As String = ""
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Requisicion
                While dr.Read()
                    Objeto = New Requisicion(CStr(dr("id")), CStr(dr("prioridad")), _
                    CStr(dr("unidad")), CStr(dr("preciounitario")), CStr(dr("cantidad")), _
                    CStr(dr("um")), CStr(dr("descripcion")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing

        End Function

        Public Function obtenerMaterial(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim ObjetoMaterial As New Material
                Dim ObjetoDatosMaterial As New MaterialDatos

                While dr.Read()
                    ObjetoMaterial.Nombre = CStr(dr("nombrematerial"))
                    ObjetoMaterial.Codigo = CStr(dr("codmaterial"))
                    ObjetoMaterial.ID = CStr(dr("idmaterial"))
                    ObjetoMaterial.DescripcionFormal = CStr(dr("descripcionformalmaterial"))
                    ObjetoMaterial.DescripcioninFormal = CStr(dr("descripcioninformalmaterial"))
                    ObjetoMaterial.STOCKMINIMO = CStr(dr("stockminimomaterial"))
                    ObjetoMaterial.RefFAmilia = CStr(dr("reffamiliamaterial"))

                    ObjetoDatosMaterial.Id = CStr(dr("idmatdatos"))
                    ObjetoDatosMaterial.Cantidad = CStr(dr("cantidadmatdatos"))
                    ObjetoDatosMaterial.FechaIngreso = CStr(dr("fechaingresomatdatos"))
                    ObjetoDatosMaterial.UbicacionColumna = CStr(dr("ubicacioncolumnamatdatos"))
                    ObjetoDatosMaterial.UbicacionFila = CStr(dr("ubicacionfilamatdatos"))
                    ObjetoDatosMaterial.FkBodega = CStr(dr("fkbodegamatdatos"))
                    ObjetoDatosMaterial.FkMaterial = CStr(dr("fkmaterialmatdatos"))

                    ObjetoMaterial.RefMaterialDatos = ObjetoDatosMaterial
                    array.Add(ObjetoMaterial)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerValorItem(ByVal query As String) As Integer
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Integer
                While dr.Read()
                    If Not IsDBNull(dr("sumatotal")) Then
                        Objeto = CInt(dr("sumatotal"))
                    Else
                        Objeto = 0
                    End If

                End While
                Me.Close()
                Return Objeto
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function ObtenerRequisicionItem(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As ItemDocumentos
                While dr.Read()
                    Objeto = New ItemDocumentos(CStr(dr("id")), CStr(dr("item")), "", _
                    CStr(dr("detalle")), CStr(dr("cantidad")), CStr(dr("preciounitario")), CStr(dr("fkmaterial")), _
                    CStr(dr("fkrequisicion")))
                    Objeto.FKProveedor = CStr(dr("fkproveedor"))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Function obtenerGuiaDespacho(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As GuiaDespacho
                While dr.Read()
                    Objeto = New GuiaDespacho(CStr(dr("giro")), _
                    CStr(dr("ciudad")), _
                    CStr(dr("comuna")), _
                    CStr(dr("id")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                Me.Close()
                Throw ex
            End Try
            Return Nothing
        End Function
#End Region

    End Class

End Namespace

